package chixing.jdbc.work;

import chixing.day20.proxy.homework.work3.Result;
import com.mysql.cj.protocol.Resultset;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EmpTest {
    public static void main(String[] args) {
        //查询所有员工
//        List<Emp> all = EmpTest.getAll();
//        all.forEach(System.out::println);
        //根据ID查询员工信息
//        Emp emp1 = EmpTest.selectById(888);
//        System.out.println(emp1);
        //插入一个员工
//        LocalDate localDate=LocalDate.now();
//        Emp emp2 = new Emp(222,"aaa","管理",7788,localDate,9876,100,10);
//        int rows = EmpTest.insert(emp2);
//        System.out.println("影响行数:"+rows);
        //修改员工信息
//        LocalDate localDate1=LocalDate.now();
//        Emp emp3=new Emp(222,"abb","管理+老板",7788,localDate1,9876,100,10);
//        int updateRows = EmpTest.update(emp3);
//        System.out.println("影响行数:"+updateRows);
        //通过员工ID删除员工对象
//        int deleteRows = EmpTest.delete(222);
//        System.out.println("影响行数:"+deleteRows);
    }
    /**
     * 连接数据库
     */
    public static Connection connectMsql(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        String url="jdbc:mysql://localhost:3306/empdb?useUnicode=true&characterEncoding=UTF-8";
        String userName="root";
        String password="root";
        Connection connection = null;
        try {
            connection= DriverManager.getConnection(url,userName,password);
            System.out.println("连接数据库成功！");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return connection;
    }

    /**
     * 查询所有员工
     * @return  员工列表
     */
    public static List<Emp> getAll(){
        Connection connection = EmpTest.connectMsql();//连接数据库
        List<Emp> empList=new ArrayList<>();
        ResultSet rs = null;
        PreparedStatement preparedStatement=null;//该类用于执行sql语句
        String sql="select empno,ename,job , mgr,hiredate,salary,comm, deptno from emp";
        try {
            preparedStatement=connection.prepareStatement(sql);
            rs=preparedStatement.executeQuery();
            while (rs.next()){
                int empNo = rs.getInt(1);
                String empName = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                LocalDate hiredate = rs.getDate(5).toLocalDate();
                double salary = rs.getDouble(6);
                double comm = rs.getDouble(7);
                int deptNo = rs.getInt(8);
                Emp emp=new Emp(empNo,empName,job,mgr,hiredate,salary,comm,deptNo);
                empList.add(emp);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (preparedStatement!=null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }
        return empList;
    }

    /**
     * 根据员工Id查询
     * @param empNo 员工ID
     * @return 员工对象
     */
    public static Emp selectById(int empNo){
        Connection connection = EmpTest.connectMsql();//连接数据库
        Emp emp=null;
        PreparedStatement preparedStatement=null;
        ResultSet rs=null;
        String sql="select empno,ename,job , mgr,hiredate,salary,comm, deptno from emp where empno = ?";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,empNo);
            rs=preparedStatement.executeQuery();
            while (rs.next()){
                empNo = rs.getInt(1);
                String empName = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                LocalDate hiredate = rs.getDate(5).toLocalDate();
                double salary = rs.getDouble(6);
                double comm = rs.getDouble(7);
                int deptNo = rs.getInt(8);
                emp=new Emp(empNo,empName,job,mgr,hiredate,salary,comm,deptNo);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (preparedStatement!=null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return emp;
    }

    /**
     * 插入一个员工对象
     * @param emp 要添加的员工对象
     * @return 影响行数
     */
    public static int insert(Emp emp){
        Connection connection = EmpTest.connectMsql();//连接数据库
        PreparedStatement preparedStatement=null;
        int rows=0;
        String sql="insert into emp values(?,?,?,?,?,?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,emp.getEmpNo());
            preparedStatement.setString(2,emp.geteName());
            preparedStatement.setString(3,emp.getJob());
            preparedStatement.setInt(4,emp.getMgr());
            preparedStatement.setDate(5,java.sql.Date.valueOf(emp.getHiredate()));
            preparedStatement.setDouble(6,emp.getSalary());
            preparedStatement.setDouble(7,emp.getComm());
            preparedStatement.setInt(8,emp.getDeptNo());
            rows=preparedStatement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (preparedStatement!=null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return rows;
    }

    /**
     * 修改员工数据
     * @param emp   员工对象
     * @return  影响行数
     */
    public static int update(Emp emp){
        Connection connection = EmpTest.connectMsql();//连接数据库
        PreparedStatement preparedStatement=null;
        int rows=0;
        String sql="update emp set ename = ?,job = ? , mgr = ? ,hiredate = ?, salary = ? , comm = ? ,deptno = ? where empno = ?";
        try {
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,emp.geteName());
            preparedStatement.setString(2,emp.getJob());
            preparedStatement.setInt(3,emp.getMgr());
            preparedStatement.setDate(4,java.sql.Date.valueOf(emp.getHiredate()));
            preparedStatement.setDouble(5,emp.getSalary());
            preparedStatement.setDouble(6,emp.getComm());
            preparedStatement.setInt(7,emp.getDeptNo());
            preparedStatement.setInt(8,emp.getEmpNo());
            rows=preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (preparedStatement!=null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return rows;
    }

    /**
     * 根据主键删除员工
     * @param empno 员工ID
     * @return 影响行数
     */
    public static int delete(int empno){
        Connection connection = EmpTest.connectMsql();//连接数据库
        PreparedStatement preparedStatement = null;
        int rows=0;
        String sql="delete from emp  where empno = ?";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,empno);
            rows=preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (preparedStatement!=null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return rows;
    }

}
